package com.estore.service.persitence;

import com.estore.service.model.Department;
import com.estore.service.persitence.core.MySQLDatabaseConnector;
import com.estore.service.util.exception.EStoreException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class DepartmentDatabaseManager {

    private Connection conn;
    private ResultSet result;

    public boolean insertDepartment(Department department) {
        boolean inserted = true;
        conn = MySQLDatabaseConnector.getConnection();
        PreparedStatement insertDepartment = null;
        try {
            insertDepartment = conn.prepareStatement("insert into department set name=?");
            insertDepartment.setString(1, department.getName());
            insertDepartment.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace(System.out);
            inserted = false;
        }
        return inserted;
    }

    public boolean updateDepartment(Department department) {
        boolean inserted = true;
        conn = MySQLDatabaseConnector.getConnection();
        PreparedStatement updateDepartment = null;
        try {
            updateDepartment= conn.prepareStatement("update department set "
                    + " name=? where id=?");
             updateDepartment.setString(1, department.getName());
            updateDepartment.setInt(2, department.getId());
            updateDepartment.executeUpdate();
        } catch (SQLException e) {            
            inserted = false;
           throw new EStoreException(e);
        }
        return inserted;
    }

   

    public Department getDepartmentById(int departmentId) {
        Department department = new Department();
        conn = MySQLDatabaseConnector.getConnection();
        PreparedStatement selectDepartmentById = null;
        try {
            selectDepartmentById = conn.prepareStatement("select * from department where id=?");
            selectDepartmentById.setInt(1, departmentId);
            result = selectDepartmentById.executeQuery();
            while (result.next()) {
                department = getDepartment(result);
            }
        } catch (SQLException e) {
            throw new EStoreException(e);
        }
        return department;
    }

    
    public Department getDepartment(ResultSet result) {
        Department department = new Department();
        try {
            department = new Department();
            department.setName(result.getString("name"));
            department.setId(result.getInt("id"));
        } catch (SQLException e) {
            e.printStackTrace(System.out);
        }
        return department;
    }

    public List<Department> getAllDepartments(int start, int size) {
        List<Department> departments = new ArrayList<Department>();
        conn = MySQLDatabaseConnector.getConnection();
        PreparedStatement selectAllDepartments = null;
        try {
            selectAllDepartments = conn.prepareStatement("select * from department LIMIT ?,?");
            selectAllDepartments.setInt(1, start);
            selectAllDepartments.setInt(2, size);
            result = selectAllDepartments.executeQuery();
            while (result.next()) {
                departments.add(getDepartment(result));
            }
        } catch (SQLException e) {
            throw new EStoreException(e);
        }
        return departments;
    }

    public int getAllDepartmentCount() {
        conn = MySQLDatabaseConnector.getConnection();
        PreparedStatement selectAllDepartmentCount = null;
        try {
            selectAllDepartmentCount = conn.prepareStatement("select count(*) from department");
            result = selectAllDepartmentCount.executeQuery();
            while (result.next()) {
                return result.getInt(1);
            }
        } catch (SQLException e) {
            throw new EStoreException(e);
        }
        return 0;

    }
}
